package businessCommonQuery

import (
	"encoding/json"
	"errors"
	"fmt"
	"go.getaway.adminspro.padmins.com/untils"
	"go.getaway.adminspro.padmins.com/untils/di"
	"go.getaway.adminspro.padmins.com/untils/request"
	"reflect"
	"regexp"
	"strconv"
	"strings"
)

type BusinessCommonQuery struct {
	Di      *di.Di
	Request request.RequestInterface
	filters map[string]interface{}
	ops     map[string]interface{}
	limit   string
	offset  string
	sort    []string
}

const db_field_pattern = "(^_([a-zA-Z0-9.]_?)*$)|(^[a-zA-Z](_?[a-zA-Z0-9.])*_?$)"

type where string
type bindValue []interface{}
type limit string
type order string

func (q *BusinessCommonQuery) parseParams() error {
	filters := q.Request.Params("filters", "")
	if filters != "" {
		if reflect.TypeOf(filters).Kind() == reflect.String {
			e := json.Unmarshal([]byte(filters.(string)), &q.filters)
			if e != nil {
				return errors.New("filters参数格式错误")
			}
		} else {
			q.filters = filters.(map[string]interface{})
		}
	}

	ops := q.Request.Params("ops", "")
	if ops != "" {
		if reflect.TypeOf(ops).Kind() == reflect.String {
			e := json.Unmarshal([]byte(ops.(string)), &q.ops)
			if e != nil {
				return errors.New("ops参数格式错误")
			}
		} else {
			q.ops = ops.(map[string]interface{})
		}
	}

	sort := q.Request.Params("sort", "")
	orderWay := strings.ToUpper(q.Request.Params("order", "ASC").(string))

	if orderWay != "ASC" && orderWay != "DESC" {
		return errors.New("invalid param order")
	}

	if sort != "" {
		if reflect.TypeOf(sort).Kind() == reflect.String {
			e := json.Unmarshal([]byte(sort.(string)), &q.sort)
			if e != nil {
				q.sort = append(q.sort, fmt.Sprintf("%s-%s", sort.(string), orderWay))
			}
		} else {
			if reflect.TypeOf(sort).Kind() != reflect.Slice {
				return errors.New("invalid param sort")
			}
			q.sort = sort.([]string)
		}
	}

	if reflect.TypeOf(q.sort).Kind() != reflect.Slice {
		return errors.New("invalid param sort")
	}

	q.limit = q.Request.Params("limit", "10").(string)
	q.offset = q.Request.Params("offset", "0").(string)

	return nil
}

func (q *BusinessCommonQuery) Build() (where, bindValue, limit, order, error) {
	e := q.parseParams()

	if e != nil {
		return "", nil, "", "", e
	}

	wheres := map[string][]interface{}{}

	for key, param := range q.filters {
		matched, e := regexp.MatchString(db_field_pattern, key)
		if e != nil {
			return "", nil, "", "", e
		}

		if matched != true {
			return "", nil, "", "", fmt.Errorf("invalid param filter[%s]", key)
		}

		op := "="
		if _, ok := q.ops[key]; ok {
			op = q.ops[key].(string)
		}

		switch strings.ToUpper(op) {
		case "=", "<>", "!=", ">", ">=", "<", "<=", "LIKE", "NOT LIKE":
			if reflect.TypeOf(param).Kind() != reflect.String && reflect.TypeOf(param).Kind() != reflect.Float64 {
				return "", nil, "", "", fmt.Errorf("invalid param filter[%s]", key)
			}
			wheres[fmt.Sprintf("%s %s ?", key, op)] = []interface{}{param}
		case "RANGE", "NOT RANGE", "BETWEEN", "NOT BETWEEN":
			op = strings.ReplaceAll(op, "RANGE", "BETWEEN")
			params := make([]interface{}, 2)
			if strings.Index(param.(string), ",") == -1 {
				for k, v := range strings.Split(strings.TrimSpace(param.(string)), " - ") {
					params[k] = v
					if k == 1 {
						break
					}
				}
			} else {
				for k, v := range strings.Split(strings.TrimSpace(param.(string)), ",") {
					params[k] = v
					if k == 1 {
						break
					}
				}
			}

			if len(params) < 2 {
				params[1] = ""
			}

			if params[0] == "" {
				if op == "BETWEEN" {
					op = "<="
				} else {
					op = ">"
				}
				param = params[1]
				wheres[fmt.Sprintf("%s %s ?", key, op)] = []interface{}{param.(string)}
			} else if params[1] == "" {
				if op == "BETWEEN" {
					op = ">="
				} else {
					op = "<"
				}
				param = params[0]
				wheres[fmt.Sprintf("%s %s ?", key, op)] = []interface{}{param.(string)}
			} else {
				wheres[fmt.Sprintf("%s %s ? AND ?", key, op)] = params
			}

		case "IN", "NOT IN":
			if reflect.TypeOf(param).Kind() != reflect.String && reflect.TypeOf(param).Kind() != reflect.Slice {
				return "", nil, "", "", fmt.Errorf("invalid param filter[%s]", key)
			}
			if reflect.TypeOf(param).Kind() == reflect.String {
				vv := []interface{}{}
				for _, v := range strings.Split(param.(string), ",") {
					vv = append(vv, v)
				}
				wheres[fmt.Sprintf("%s %s (?)", key, op)] = []interface{}{vv}
			} else {
				wheres[fmt.Sprintf("%s %s (?)", key, op)] = []interface{}{param}
			}
		default:
			return "", nil, "", "", fmt.Errorf("invalid param ops[%s]", op)
		}
	}

	andWhere := []string{}
	aBindValue := []interface{}{}

	for whes, params := range wheres {
		andWhere = append(andWhere, whes)
		aBindValue = append(aBindValue, params...)
	}
	w := ""
	if len(andWhere) > 0 {
		w = " AND " + untils.Implode(" AND ", andWhere)
	}

	if q.limit != "false" {
		nLimit, e := strconv.Atoi(q.limit)
		if e != nil {
			return "", nil, "", "", fmt.Errorf("limit参数错误")
		}
		nOffset := 0
		nOffset, e = strconv.Atoi(q.offset)
		if e != nil || nOffset < 0 {
			return "", nil, "", "", fmt.Errorf("offset参数错误")
		}
		if e == nil && nLimit > 0 {
			q.limit = fmt.Sprintf(" limit %d,%d", nOffset, nLimit)
		} else {
			q.limit = ""
		}
	} else {
		q.limit = ""
	}

	sOrder := ""
	if len(q.sort) > 0 {
		for _, sort := range q.sort {
			aSort := strings.Split(sort, "-")
			sortField := ""
			orderWay := "ASC"
			sortField = aSort[0]
			if len(aSort) > 1 {
				orderWay = aSort[1]
			}

			matched, e := regexp.MatchString(db_field_pattern, sortField)
			if e != nil {
				return "", nil, "", "", e
			}

			if matched != true {
				return "", nil, "", "", fmt.Errorf("invalid param sort[%s]", sortField)
			}

			orderWay = strings.ToUpper(orderWay)
			if orderWay != "ASC" && orderWay != "DESC" {
				return "", nil, "", "", fmt.Errorf("invalid param sort[%s] order[%s]", sortField, orderWay)
			}

			sOrder += fmt.Sprintf("%s %s,", sortField, orderWay)
		}
		sOrder = "order by " + strings.Trim(sOrder, ",")
	}

	return where(strings.TrimSpace(w)), bindValue(aBindValue), limit(strings.TrimSpace(q.limit)), order(sOrder), nil
}
